package com.example.demo;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.Date;

public class ExcelData {

  private XSSFSheet sheet;

  private Cell cell;

  ExcelData(String filePath, String sheetName) {
    FileInputStream fis = null;
    try {
      fis = new FileInputStream(filePath);
      XSSFWorkbook sheets = new XSSFWorkbook(fis);
      // 获取sheet
      sheet = sheets.getSheet(sheetName);
    } catch (Exception e) {
      System.out.println("出错了");
    }
  }

  public String getExcelDateByIndex(int row, int column) {
    XSSFRow row1 = sheet.getRow(row);
    Cell cell = row1.getCell(column);
    String s = cell.getCellStyle().getDataFormatString();
    if (cell.getCellStyle().getDataFormatString().equals("m/d/yy")) {
      Date theDate = cell.getDateCellValue();
      SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
      String ret = simpleDateFormat.format(theDate);
      return ret;
    }
    if (cell.getCellStyle().getDataFormatString().equals("h:mm:ss")) {
      SimpleDateFormat sdf = new SimpleDateFormat("H:mm:ss");
      Date date = cell.getDateCellValue();
      return sdf.format(date);
    }
    if (cell.getCellStyle().getDataFormatString().equals("0.00")) {
      return String.valueOf(cell.getNumericCellValue()) + 0;
    }
    return cell.toString();
  }

  public Cell getCell() {
    return cell;
  }

  public String getCellByCaseName(String caseName, int currentColumn, int targetColumn) {
    String operateSteps = "";
    // 获取行数
    int rows = sheet.getPhysicalNumberOfRows();
    for (int i = 0; i < rows; i++) {
      XSSFRow row = sheet.getRow(i);
      String cell = row.getCell(currentColumn).toString();
      if (cell.equals(caseName)) {
        operateSteps = row.getCell(targetColumn).toString();
        break;
      }
    }
    return operateSteps;
  }

  // 打印excel数据
  public void readExcelData() {
    // 获取行数
    int rows = sheet.getPhysicalNumberOfRows();
    for (int i = 0; i < rows; i++) {
      // 获取列数
      XSSFRow row = sheet.getRow(i);
      int columns = row.getPhysicalNumberOfCells();
      for (int j = 0; j < columns; j++) {
        String cell = row.getCell(j).toString();
        System.out.println(cell);
      }
    }
  }

  // 测试方法
  public static void main(String[] args) {
    ExcelData excelData = new ExcelData("C:\\Users\\cwy\\Desktop\\mdb\\1.xls", "Sheet1");
    String empiricalName = excelData.getExcelDateByIndex(0, 0); // 获取型号
    empiricalName = empiricalName.substring(0, 11);
    String experimentNumber = excelData.getExcelDateByIndex(1, 1); // 获取编号
    String experimentTime = excelData.getExcelDateByIndex(1, 6); // 获取时间
    String experimentDate = excelData.getExcelDateByIndex(1, 5); // 获取日期
    String riseTime = excelData.getExcelDateByIndex(3, 0); // 获取上升时间
    String ascendingStroke = excelData.getExcelDateByIndex(3, 1); // 获取上升行程
    String brakingTorque = excelData.getExcelDateByIndex(3, 2); // 获取制动力矩
    String descentTime = excelData.getExcelDateByIndex(3, 3); // 获取下降时间
    String descentStroke = excelData.getExcelDateByIndex(3, 4); // 获取下降行程
    String InsulationTest = excelData.getExcelDateByIndex(3, 5); // 获取500v绝缘测试
    String HiPot = excelData.getExcelDateByIndex(3, 6); // 获取耐压测试


     System.out.println(empiricalName+","+experimentNumber+","+experimentTime+","+experimentDate+","+riseTime+","+ascendingStroke+","+brakingTorque+","+descentTime
        +","+descentStroke+","+InsulationTest+","+HiPot);
    //    System.out.println(experimentDate);
    //    System.out.println(experimentTime);
    //    System.out.println(empiricalName);
    //    System.out.println(riseTime);
    //    System.out.println(ascendingStroke);
  }
}
